﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using System.Data;
using KPIS.GERP.GHRMS.DAL;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IBLL; 
using KPIS.DBM;

using KPIS.GERP.GHRMS.DALFactory;

namespace KPIS.GERP.GHRMS.BLL
{
    public class CommandDisciplineBLL
    {
        DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        public void insert(string pen_name, string record_status,int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);
                dbManager.AddParameters(0, "@pen_name", pen_name);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@created_by", created_by);
              //  dbManager.AddParameters(3, "@created_when", DateTime.Today.ToString() );
           
  
                string sql;
   //             sql = " insert into hrm_dc_penalty(pen_name,record_status,created_by,created_when) "
   //+ " values(@pen_name,@record_status,@created_by,@created_when)";


                sql = " insert into hrm_dc_penalty(pen_name,record_status,created_by) "
+ " values(@pen_name,@record_status,@created_by)";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);    

            }

            catch(Exception ex)
             {

                 throw new Exception(ex.Message);  
            }

          finally 
                 {
                     dbManager.Dispose(); 

            }

        }



        public DataTable Select()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT * from hrm_ps_commands  "

+ " where cmd_type_seq = 901 and "
          + " record_status = '" + SystemInfo._ActiveRecordStatus   + "'";
                    

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectEmpTypeCombo()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT * from hrm_ps_employee_types  "

+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectInvestigationCombo()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT * from hrm_dc_investigations  "

+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectPenaltyLevelCombo()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT * from hrm_dc_penalty_levels "

+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectCrimeDtlsCombo()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " SELECT * from hrm_dc_crime_dtls "

+ " where record_status = '" + SystemInfo._ActiveRecordStatus + "'";


                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }

        public DataTable SelectStatus()
        {
            try
            {
                dbManager.Open();
                string sql;
                sql = " select * from mas_record_statuses  "
                    + " where record_status in('" + SystemInfo._ActiveRecordStatus  + "','" + SystemInfo._CancelRecordStatus  + "')";
             
                DataSet ds = new DataSet();
                DataTable dt;
                                ds = dbManager.ExecuteDataSet(CommandType.Text, sql);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();  
            }

        }


        public void Update(int pen_seq,string pen_name, string record_status, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);
                dbManager.AddParameters(0, "@pen_name", pen_name);
                dbManager.AddParameters(1, "@record_status", record_status);
                dbManager.AddParameters(2, "@pen_seq", pen_seq);
                dbManager.AddParameters(3, "@updated_by", updated_by);
               dbManager.AddParameters(4, "@updated_when", DateTime.Today);


                string sql;
       
                sql = " update hrm_dc_penalty "
         + "set pen_name=@pen_name,record_status=@record_status,updated_by=@updated_by,updated_when=@updated_when "
         + " where pen_seq=@pen_seq";


   

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }

            catch (Exception ex)
            {
            }

            finally
            {
                dbManager.Dispose();

            }

        }
 
        public void Delete(int pen_seq,int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@pen_seq", pen_seq);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;
             
                sql = " update hrm_dc_penalty set "
              + " record_status=@record_status"
              + ", updated_by = @updated_by"
              + ", updated_when = @updated_when"
              + " where pen_seq = @pen_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);
      
            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        //***************** คำสั่งตั้งคณะกรรมการ ***********************
        public DataTable SelectCommittee(int CmdSeq)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@cmd_seq", CmdSeq);

                string strSQL;

                strSQL = "SELECT cc.COM_CMD_SEQ, cc.CMD_SEQ, cc.EMP_SEQ, cc.POS_SEQ, cc.CLASS_SEQ, cc.JOB_SITE_SEQ, cc.COM_POS_SEQ"
                    + ", cc.REMARKS, cc.RECORD_STATUS, cc.CREATED_BY, cc.CREATED_WHEN"
                    + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS CMM_NAME"
                    + ", p.POS_REF, p.POS_NAME, c.CLASS_NAME, j.JOB_SITE_NAME, cp.COM_POS_NAME"
                    + " FROM HRM_DC_COMMITTEE_COMMANDS cc"
                    + " LEFT JOIN HRM_PS_EMPLOYEES e ON cc.EMP_SEQ = e.EMP_SEQ"
                    + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " LEFT JOIN HRM_MP_POSITIONS p ON cc.POS_SEQ = p.POS_SEQ"
                    + " LEFT JOIN HRM_MP_CLASSES c ON cc.CLASS_SEQ = c.CLASS_SEQ"
                    + " LEFT JOIN HRM_MP_JOB_SITES j ON cc.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                    + " LEFT JOIN HRM_DC_COMMITTEE_POSITIONS cp ON cc.COM_POS_SEQ = cp.COM_POS_SEQ"
                    + " WHERE cc.RECORD_STATUS = @record_status"
                    + " AND cc.CMD_SEQ = @cmd_seq";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }
        public DataTable SelectCommitteePosition()
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@record_status", SystemInfo._ActiveRecordStatus);

                string strSQL;

                strSQL = "SELECT COM_POS_SEQ, COM_POS_NAME"
                    + " FROM HRM_DC_COMMITTEE_POSITIONS"
                    + " WHERE RECORD_STATUS = @record_status";

                DataSet ds = new DataSet();
                DataTable dt;
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
                dt = ds.Tables[0].Copy();
                return dt;
            }

            catch (Exception ex)
            {
                return null;
            }

            finally
            {
                dbManager.Dispose();
            }

        }
        public void InsertCommittee(int cmd_seq, int emp_seq, int com_pos_seq, string remarks, int created_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@cmd_seq", cmd_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@com_pos_seq", com_pos_seq);
                dbManager.AddParameters(3, "@remarks", remarks);
                dbManager.AddParameters(4, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@created_by", created_by);
                dbManager.AddParameters(6, "@created_when", DateTime.Today);                

                string strSQL ;

                strSQL = "INSERT INTO HRM_DC_COMMITTEE_COMMANDS"
                    + "(CMD_SEQ, EMP_SEQ, POS_SEQ, CLASS_SEQ, JOB_SITE_SEQ, COM_POS_SEQ"
                    + ", REMARKS, RECORD_STATUS, CREATED_BY, CREATED_WHEN)"
                    + " SELECT @cmd_seq AS CMD_SEQ"
                        + ", e.emp_seq AS EMP_SEQ"
                        + ", e.POS_SEQ, e.CLASS_SEQ, p.JOB_SITE_SEQ, @com_pos_seq AS COM_POS_SEQ"
                    + ", @remarks AS REMARKS"
                    + ", @record_status AS RECORD_STATUS"
                    + ", @created_by AS CREATED_BY, @created_when AS CREATED_WHEN"
                    + " FROM HRM_PS_EMPLOYEES e"
                    + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                    + " WHERE e.EMP_SEQ = @emp_seq"
                        + " AND e.RECORD_STATUS = @record_status";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }

            catch (Exception ex)
            {
            }

            finally
            {
                dbManager.Dispose();
            }

        }
        public void UpdateCommittee(int cmd_dtls_seq, int cmd_seq, int emp_seq, int com_pos_seq
            , string remarks, int updated_by)
        {
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(7);
                dbManager.AddParameters(0, "@cmd_dtls_seq", cmd_dtls_seq);
                dbManager.AddParameters(1, "@emp_seq", emp_seq);
                dbManager.AddParameters(2, "@com_pos_seq", com_pos_seq);
                dbManager.AddParameters(3, "@remarks", remarks);
                dbManager.AddParameters(4, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(5, "@updated_by", updated_by);
                dbManager.AddParameters(6, "@updated_when", DateTime.Today);

                string strSQL;

                strSQL = "UPDATE HRM_DC_COMMITTEE_COMMANDS"
                    + " SET EMP_SEQ = @emp_seq"
                        + ", POS_SEQ = (select pos_seq from hrm_ps_employees where emp_seq = @emp_seq)"
                        + ", CLASS_SEQ = (select class_seq from hrm_ps_employees where emp_seq = @emp_seq)"
                        + ", JOB_SITE_SEQ = (select e.pos_seq from hrm_ps_employees e"
                                        + " left join hrm_mp_positions p on e.pos_seq = p.pos_seq"
                                        + " where e.emp_seq = @emp_seq)"
                        + ", COM_POS_SEQ = @com_pos_seq"
                        + ", REMARKS = @remarks"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE COM_CMD_SEQ = @cmd_dtls_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

            }
                 
            catch (Exception ex)
            {
            }

            finally
            {
                dbManager.Dispose();
            }

        }
        public void DeleteCommittee(int sequence, int UpdatedBy)
        {
            try
            {

                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@sequence", sequence);
                dbManager.AddParameters(2, "@updated_by", UpdatedBy);
                dbManager.AddParameters(3, "@updated_when", DateTime.Today);

                string sql;

                sql = " UPDATE HRM_DC_COMMITTEE_COMMANDS set "
                      + " RECORD_STATUS = @record_status"
                      + ", UPDATED_BY = @updated_by"
                      + ", UPDATED_WHEN = @updated_when"
                      + " WHERE COM_CMD_SEQ = @sequence";

                dbManager.ExecuteNonQuery(CommandType.Text, sql);

            }
            catch (Exception ex)
            {
                throw new Exception("");
            }
            finally
            {
                dbManager.Dispose();
            }
        }
        //********************************************************

    }
}
